package com.ai.imagetext.repository;

import com.ai.imagetext.entity.ImageTag;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

@Repository
public interface ImageTagRepository extends JpaRepository<ImageTag, Long> {
    
    /**
     * 根据图片ID查找所有标签关联
     */
    List<ImageTag> findByImageId(Long imageId);
    
    /**
     * 根据标签ID查找所有图片关联
     */
    List<ImageTag> findByTagId(Long tagId);
    
    /**
     * 根据图片ID和标签ID查找关联
     */
    Optional<ImageTag> findByImageIdAndTagId(Long imageId, Long tagId);
    
    /**
     * 根据图片ID和标签来源查找关联
     */
    List<ImageTag> findByImageIdAndSource(Long imageId, ImageTag.TagSource source);
    
    /**
     * 根据置信度范围查找关联
     */
    @Query("SELECT it FROM ImageTag it WHERE it.confidence BETWEEN :minConfidence AND :maxConfidence")
    List<ImageTag> findByConfidenceBetween(@Param("minConfidence") Double minConfidence, 
                                          @Param("maxConfidence") Double maxConfidence);
    
    /**
     * 根据标签来源统计数量
     */
    long countBySource(ImageTag.TagSource source);
    
    /**
     * 根据图片ID统计标签数量
     */
    long countByImageId(Long imageId);
    
    /**
     * 根据标签ID统计图片数量
     */
    long countByTagId(Long tagId);
    
    /**
     * 删除图片的所有标签关联
     */
    @Modifying
    @Query("DELETE FROM ImageTag it WHERE it.image.id = :imageId")
    void deleteByImageId(@Param("imageId") Long imageId);
    
    /**
     * 删除标签的所有图片关联
     */
    @Modifying
    @Query("DELETE FROM ImageTag it WHERE it.tag.id = :tagId")
    void deleteByTagId(@Param("tagId") Long tagId);
    
    /**
     * 删除指定的图片标签关联
     */
    @Modifying
    @Query("DELETE FROM ImageTag it WHERE it.image.id = :imageId AND it.tag.id = :tagId")
    void deleteByImageIdAndTagId(@Param("imageId") Long imageId, @Param("tagId") Long tagId);
    
    /**
     * 获取图片的标签名称列表
     */
    @Query("SELECT t.name FROM ImageTag it JOIN it.tag t WHERE it.image.id = :imageId ORDER BY it.confidence DESC")
    List<String> findTagNamesByImageId(@Param("imageId") Long imageId);
    
    /**
     * 根据标签名称查找相关图片ID
     */
    @Query("SELECT DISTINCT it.image.id FROM ImageTag it JOIN it.tag t WHERE t.name = :tagName")
    List<Long> findImageIdsByTagName(@Param("tagName") String tagName);
    
    /**
     * 获取标签使用统计
     */
    @Query("SELECT t.name, COUNT(it) as usage_count FROM ImageTag it JOIN it.tag t " +
           "GROUP BY t.id, t.name ORDER BY usage_count DESC")
    List<Object[]> getTagUsageStatistics();
}